package com.eascs.admin.o2o.submitted.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.o2o.submitted.entity.O2oLoanSubmittedEntity;
import com.eascs.admin.o2o.submitted.vo.O2oLoanSubmittedVo;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

@Repository
public class O2oLoanSubmittedDao extends BaseO2oDao {

	public O2oLoanSubmittedEntity getEntityByLoanApplyNo(String loanapplyNo) {
		String sql = "select * from o2o_loan_submitted ols where ols.is_del = 'N' and ols.loanapp_no = ? ";
		return this.findEntity(sql, O2oLoanSubmittedEntity.class, loanapplyNo);
	}

	public PageResult<Object[]> queryEntitys(O2oLoanSubmittedVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		
//		String querySql = "sql.o2o.submitted.querybypage";
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT OLS.ID,OLS.FLOW_NUMBER,OLS.BILL_CODE,OLS.LOAN_USER_NAME,OLS.PROVINCE_NAME,");
		sql.append("OLS.LTD_NAME,OLS.OPERATOR_NAME,OLS.LOAN_MOUNT,DATE_FORMAT(OLS.LOAN_EXPIRE_DATE,'%Y-%m-%d') AS LOAN_EXPIRE_DATE,");
		sql.append("OLS.FLOW_STATUS,OLS.CREATE_TIME ");
		sql.append(" FROM O2O_LOAN_SUBMITTED OLS ,o2o_loanapply l ");
		sql.append(" WHERE   OLS.IS_DEL='N' and OLS.loanapp_no=l.loanapply_no");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(vo.getUsersno())) {
			sql.append(" or l.operator_no in ("+vo.getUsersno()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/ 1=1 ) ");
		}
		
		if (StringUtil.isNotBlank(vo.getBillCode())) {
			sql.append(" AND OLS.BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (StringUtil.isNotBlank(vo.getLoanUserName())) {
			sql.append(" AND OLS.LOAN_USER_NAME LIKE ?");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (StringUtil.isNotBlank(vo.getFlowNumber())) {
			sql.append(" AND OLS.FLOW_NUMBER LIKE ?");
			sqlPageQuery.addLikeParam(vo.getFlowNumber());
		}
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	public List<Object[]> findscommon(String code) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ol.loanapply_no,ol.bill_code,CONCAT(ol.bill_code,'-',olu.user_full_name),olu.user_full_name ");
		sql.append(" ,ol.end_bank_date from o2o_loanapply ol LEFT JOIN o2o_loan_user olu on ol.loan_id = olu.loan_id ");
		sql.append(" join o2o_ea_loan_data oeld on ol.bill_code = oeld.bill_code_ea and oeld.is_del='N' ");
		sql.append(" where /**DATA_PERMISSION(ol.LTD)**/ olu.spouse_id is null and olu.loanapply_no=ol.loanapply_no ");
		sql.append(" and ol.bill_code like ? GROUP BY ol.bill_code LIMIT 10");
		return this.findObjectsList(sql.toString(), "%" + code + "%");
	}
}
